|
 |
 |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96
Chapter 10 Performance Enhancements
This chapter continues where the last chapter left off. This chapter continues to look at the Oracle server and additional things you can do to improve system performance. Because these items are essentially an unrelated list of individual topics, the chapter is arranged alphabetically. Dont interpret the order of the topics to mean that one of these is more important than another.
First, the chapter looks at why you may want to change the block size of the database. Then it looks in detail at the concept of clusters and how they can benefit your configuration. The chapter also looks at indexes: when you should use one and how.
You also look at some of the optional products Oracle has to offer (such as the Parallel Query option and the Parallel Server option) and how they are used.
At the end of the chapter are several miscellaneous items such as spin count that may affect performance in your system.
Block Size
Typically, the default block size is sufficient for all applications. In a few cases, however, increasing the size of the database buffers is beneficial. Depending on your operating system, there are different limitations on the value of DB_BLOCK_SIZE.
If you have a large system with many disks, think about increasing the database block size. These very large systems typically use a block size of 4K or larger. The larger block size may benefit the overall performance of the system.
The size of the database block has several effects of which you should be aware. If the block size is too small for the data stored in the database, you suffer the following effects:
- Unnecessary I/O, caused by the additional chaining and migration of rows that do not fit in the block.
- Unnecessary latch operations, caused by redo information not fitting in the redo log buffer.
- Wasted space, caused because the smaller blocks are less likely to be entirely filled.
All these problems result in unnecessary overhead in space, CPU, and I/Os. By increasing the block size, many of these problems can be eliminated.
But be careful. A block size that is too large can cause the following effects on your system:
- More I/Os. Because the block size is larger, there are fewer database block buffers in memory for the same size SGA. This situation results in more I/Os because buffers must written out to make room for new ones.
- Larger I/Os. The larger the I/O, the longer it takes to copy data into memory. But if the large amount of data is needed, one long I/O is much better than two shorter I/Os. See Chapter 14, Advanced Disk I/O Concepts, for more information on disk retrieval time.
- Disk queuing takes longer. Because of the longer I/Os, disk queuing takes longer, which is also worthwhile if the data you retrieved is useful.
Other effects of a larger block size include a reduction in the depth of the B*-tree index and less disk-seek overhead to retrieve data blocks. Another effect of a larger block size is an increase in the size of cluster buckets.
Here are a few guidelines to help you decide whether changing the size of DB_BLOCK_SIZE can benefit you:
- OLTP systems benefit from smaller blocks. If your application is OLTP in nature, you will not benefit from larger blocks. OLTP data typically fits well in the default block size; larger blocks unnecessarily eject blocks from the SGA.
- DSS systems benefit from larger blocks. In the DSS system where table scans are common, retrieving more data at a time results in a performance increase.
- Larger databases benefit from larger blocks. Larger databases see a space benefit because there is less waste per block.
- Databases with large rows benefit from larger blocks. If your rows are extremely large (such as images or text) and dont fit in the default block, you will see a definite benefit from a larger block size.
When changing the block size, be careful to set DB_BLOCK_SIZE only to a multiple of the OS block size. Doing so guarantees that you are not reading OS blocks and only using part of the block. For example, if you use 1K Oracle blocks and 2K OS blocks, the OS reads 2K from the disk every time you request a 1K block.
The database block size should be changed only after careful consideration. If you see excessive chained rows or wasted space, consider a larger block size. If you are running a DSS system, you may also want to consider a larger block size. Larger block sizes can be very beneficialbut can be detrimental if you change them unnecessarily.
|